<?php
class Pembayaran_bulanan_model extends CI_Model 
{

	function __construct()
	{
		parent::__construct();
	}
	
	function insert_data($id_periode,$id_komponen,$nominal,$nis){
		$tgl=date('Y-m-d');
		$data=array('id_periode'=>$id_periode,
					'tgl_bayar'=>$tgl,
					'nis'=>$nis,
					'id_komponen'=>$id_komponen,
					'nominal'=>$nominal);
		$insert=$this->db->insert('pembayaran_bulanan',$data);
		return $insert;
	}
	
	function insert_tagihan($id_periode,$id_komponen,$nominal,$nis){
		$data=array('id_periode'=>$id_periode,
					'nis'=>$nis,
					'id_komponen'=>$id_komponen,
					'nominal'=>$nominal);
		$insert=$this->db->insert('pembayaran_bulanan',$data);
		return $insert;
	}
	
	function bayar_tagihan($id){
		$tgl_bayar=date('Y-m-d');
		$data=array('status_bayar'=>'ya',
					'tgl_bayar'=>$tgl_bayar);
		$this->db->where('id',$id);
		$update=$this->db->update('pembayaran_bulanan',$data);
		return $update;
	}
	
	function delete($id){
		$data=array('status_bayar'=>'tidak',
					'tgl_bayar'=>null);
		$this->db->where('id',$id);
		$update=$this->db->update('pembayaran_bulanan',$data);
		return $update;
	}
	
	function update_tagihan($id,$nominal){
		$data=array('nominal'=>$nominal);
		$this->db->where('id',$id);
		$update=$this->db->update('pembayaran_bulanan',$data);
		return $update;
	}

	
	function get_bayar($nis,$id_komp){
		$sql="select IFNULL(SUM(nominal),0) as total from pembayaran_bulanan 
			where nis='".$nis."' and id_komponen='".$id_komp."'";
		//echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			$hasil= $query->row_array();
			return $hasil['total'];
		}else{
			return 0;
		}
	}
	
	function get_bayar_bulanan($nis,$id_komp,$tgl_awal,$tgl_akhir){
		$sql="select IFNULL(SUM(nominal),0) as total from pembayaran_bulanan 
			where nis='".$nis."' and id_komponen='".$id_komp."' and status_bayar='ya' 
			and tgl_bayar between '".$tgl_awal."' and '".$tgl_akhir."'";
		//echo $sql.'<br>';
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			$hasil= $query->row_array();
			return $hasil['total'];
		}else{
			return 0;
		}
	}
	
	function get_tagihan_lunas($nis,$id_komp,$id_tapel){
		$sql="SELECT a.*,b.periode FROM pembayaran_bulanan a left join mr_periode_tapel b on a.id_periode=b.id 
				where id_periode in( select id from mr_periode_tapel 
				WHERE id_tahun_ajaran='".$id_tapel."') and 
				id_komponen='".$id_komp."' and nis='".$nis."' and status_bayar='ya' ORDER BY id_periode ASC";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
	function get_tagihan($nis,$id_komp,$id_tapel){
		$sql="SELECT *,id as id_periode FROM mr_periode_tapel WHERE  id_tahun_ajaran='".$id_tapel."'
			AND id NOT IN(SELECT id_periode FROM pembayaran_bulanan 
			WHERE nis='".$nis."' AND id_komponen='".$id_komp."') ORDER BY urutan ASC";
		//echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
	function get_tagihan_belum_bayar($nis,$id_komp,$id_tapel){
		$sql="select a.*,b.periode from pembayaran_bulanan a left join mr_periode_tapel b on a.id_periode=b.id
				where id_tahun_ajaran='".$id_tapel."' and nis='".$nis."' 
				and id_komponen='".$id_komp."' and a.status_bayar='tidak'";
		//echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
	function cek_tagihan($nis,$id_komp,$id_tapel){
		$sql="select * from pembayaran_bulanan where nis='".$nis."' and id_komponen='".$id_komp."' 
				and id_periode in (select id from mr_periode_tapel where id_tahun_ajaran='".$id_tapel."') ";
		//echo $sql;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return true;
		}else{
			return false;
		}	
	}
	
	function get_tagihan_sekarang($nis,$id_komp,$id_tapel,$periode){
		$sql="SELECT * FROM mr_periode_tapel WHERE periode <='".$periode."' AND id_tahun_ajaran='".$id_tapel."'
			AND id NOT IN(SELECT id_periode FROM pembayaran_bulanan 
			WHERE nis='".$nis."' AND id_komponen='".$id_komp."') ORDER BY urutan ASC";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
	function get_total_tagihan($nis,$id_komp,$periode_awal,$periode_akhir){
		$sql="SELECT IFNULL(SUM(nominal),0) AS total FROM pembayaran_bulanan a 
				WHERE  a.id_komponen='".$id_komp."'	and a.nis='".$nis."'	
				AND (id_periode between ".$periode_awal." and ".$periode_akhir.") and status_bayar='tidak'";
		//echo $sql."<br>";
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			$hasil= $query->row_array();
			return $hasil['total'];
		}else{
			return array();
		}
	}
	
	function get_tagihan_siswa_belum_set($id_tapel){
		$sql="select * from 
			(select a.nis,a.nm_lengkap,b.th_ajaran,a.id_tapel_masuk,a.nva,a.id_lembaga,c.nama_lembaga,
			(select count(*) from pembayaran_bulanan pb left join mr_periode_tapel pt on pb.id_periode=pt.id 
				where nis=a.nis and id_tahun_ajaran='".$id_tapel."')as banyak 
				from mr_siswa a left join mr_th_ajaran b on a.id_tapel_masuk=b.id_mr_th_ajaran
				left join mr_lembaga c on a.id_lembaga=c.id_lembaga
				)AS data_raw where banyak =0 ";
		//echo $sql;exit;
		$query=$this->db->query($sql);
		if ($query->num_rows()>0){
			return $query->result_array();
		}else{
			return array();
		}
	}
	
}

?>